﻿using Cxx.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Collections;
using Cxx.Entity.Extend;
using Cxx.Common;
using Cxx.IDAL;

namespace Cxx.DAL
{
    public class Order : DataAccess, IOrder
    {
        public void Update(OrderInfo info)
        {
            string sql =
                "UPDATE tblorder SET OrderCode=@OrderCode,Receiver=@Receiver,Amount=@Amount,Detail=@Detail,Paymented=@Paymented,Remark=@Remark,Time=@Time,HasOrder=@HasOrder WHERE Id=@Id";
            using (IDbConnection dbConnection = GetConnection())
            {
                dbConnection.Execute(sql, info);
            }
        }

        public int Insert(OrderInfo info)
        {
            string sql =
                "INSERT INTO tblorder (OrderCode,Receiver,Amount,Detail,Paymented,Remark,Time,HasOrder) VALUES(@OrderCode,@Receiver,@Amount,@Detail,@Paymented,@Remark,@Time,@HasOrder);SELECT @@IDENTITY";
            using (IDbConnection dbConnection = GetConnection())
            {
                return dbConnection.ExecuteScalar<int>(sql, info);
            }
        }

        public OrderInfo Get(int id)
        {
            object para = new {Id = id};
            string sql = "SELECT * FROM tblorder WHERE Id=@Id";
            using (IDbConnection dbConnection = GetConnection())
            {
                return dbConnection.QueryFirstOrDefault<OrderInfo>(sql, para);
            }
        }


        /// <summary>
        /// 分页获取列表
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public List<OrderInfo> GetListForPage(string condition, int pageIndex, int pageSize, out int recordCount)
        {
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@_tables", "tblOrder", DbType.String, null, 1000);
            //parameters.Add("@PK", "Id", DbType.String, null, 100);
            parameters.Add("@_orderby", "Id DESC", DbType.String, null, 200);
            parameters.Add("@_pageindex", pageIndex, DbType.Int32);
            parameters.Add("@_pageSize", pageSize, DbType.Int32);
            parameters.Add("@_fields", "*", DbType.String, null, 1000);

            #region 获取查询条件
            Hashtable fields = new Hashtable();
            var field = new DataFieldInfo("Time", "DateTime", "tblOrder", ">=");
            fields.Add("txtStartTime", field);
            field = new DataFieldInfo("Time", "DateTime", "tblOrder", "<=");
            fields.Add("txtEndTime", field);
            field = new DataFieldInfo("OrderCode", "Character", "tblOrder", "=");
            fields.Add("txtOrderCode", field);
            field = new DataFieldInfo("Receiver", "Character", "tblOrder", "=");
            fields.Add("txtCustomerName", field);
            field = new DataFieldInfo("Paymented", "Integer", "tblOrder", "=");
            fields.Add("ddlProductionStatus", field);
            field = new DataFieldInfo("HasOrder", "Integer", "tblOrder", "=");
            fields.Add("ddlHasOrder", field);

            #endregion
            string sqlWhere = SqlFilter.Build(condition, fields);

            parameters.Add("@_where", sqlWhere, DbType.String, null, 1000);
            parameters.Add("@_totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            parameters.Add("@_pagecount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            using (IDbConnection dbConnection = GetConnection())
            {
                var result = dbConnection.Query<OrderInfo>(USP_PAGING_PROCEDURE, parameters,
                    commandType: CommandType.StoredProcedure);
                if (result == null)
                {
                    recordCount = 0;
                    return null;
                }
                recordCount = parameters.Get<int>("@_totalcount");
                return result.ToList();
            }
        }

        public List<OrderInfo> GetList(string condition)
        {
            #region 获取查询条件
            Hashtable fields = new Hashtable();
            var field = new DataFieldInfo("Time", "DateTime", "tblOrder", ">=");
            fields.Add("txtStartTime", field);
            field = new DataFieldInfo("Time", "DateTime", "tblOrder", "<=");
            fields.Add("txtEndTime", field);
            field = new DataFieldInfo("OrderCode", "Character", "tblOrder", "=");
            fields.Add("txtOrderCode", field);
            field = new DataFieldInfo("Receiver", "Character", "tblOrder", "=");
            fields.Add("txtCustomerName", field);
            field = new DataFieldInfo("Paymented", "Integer", "tblOrder", "=");
            fields.Add("ddlProductionStatus", field);
            field = new DataFieldInfo("HasOrder", "Integer", "tblOrder", "=");
            fields.Add("ddlHasOrder", field);
            #endregion

            string sqlWhere = SqlFilter.Build(condition, fields);
            string sql = "SELECT * FROM tblOrder";
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                sql += " WHERE " + sqlWhere;
            }
            using (IDbConnection dbConnection = GetConnection())
            {
                return dbConnection.Query<OrderInfo>(sql)?.ToList();
            }
        }

        /// <summary>
        /// 计算总额
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int CalculateTotal(string condition)
        {
            #region 获取查询条件
            Hashtable fields = new Hashtable();
            var field = new DataFieldInfo("Time", "DateTime", "tblOrder", ">=");
            fields.Add("txtStartTime", field);
            field = new DataFieldInfo("Time", "DateTime", "tblOrder", "<=");
            fields.Add("txtEndTime", field);
            field = new DataFieldInfo("Receiver", "Character", "tblOrder", "=");
            fields.Add("txtCustomerName", field);
            #endregion

            string sqlWhere = SqlFilter.Build(condition, fields);
            string sql = "SELECT SUM(Amount) FROM tblOrder";
            if (!string.IsNullOrWhiteSpace(sqlWhere))
            {
                sql += " WHERE " + sqlWhere;
            }
            using (IDbConnection dbConnection = GetConnection())
            {
                return dbConnection.QueryFirstOrDefault<int>(sql);
            }
        }

        public bool ExistCustomer(string name)
        {
            string sql = "SELECT * FROM tblOrder WHERE Receiver=@Receiver LIMIT 1";
            object para = new {Receiver = name};
            using (IDbConnection dbConnection = GetConnection())
            {
                return dbConnection.QueryFirstOrDefault<OrderInfo>(sql, para) != null;
            }
        }
    }
}
